//-------------------------------------------------------------------
//文件名称：EVideoLib.cs
//模块名称：EVideoLib数据访问层
//功能说明：
//-----------------------------------------------------------------
//修改记录：
//修改人：Dawen
//-----------------------------------------------------------------

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using ELearning.Models;
using ELearning.Models.ViewModel.YTJYAdmin.VideoLib;

namespace ELearning.DAL
{
    /// <summary>
    /// EVideoLib数据访问层
    /// </summary>
	public partial class EVideoLibRepository : BaseRepository<EVideoLib>
    {
        /// <summary>
        /// 后台获取视频列表
        /// </summary>
	    public List<VideoLibModel> GetVideoLibList(VideoLibRequest request, string orgCode)
        {
            var where = string.Empty;
            if (!string.IsNullOrEmpty(request.VideoName))
                where = $" AND v.Name LIKE N'%{request.VideoName}%'";

            var args = new List<SqlParameter>();
            if (!string.IsNullOrEmpty(request.CategoryId))
            {
                where = $" {where} AND v.CategoryId = @CategoryId";
                args.Add(new SqlParameter("@CategoryId", request.CategoryId));
            }

            if (!string.IsNullOrEmpty(request.TypeCode))
            {
                where = $" {where} AND v.TypeCode = @TypeCode";
                args.Add(new SqlParameter("@TypeCode", request.TypeCode));
            }

            if (!string.IsNullOrEmpty(orgCode))
            {
                where = $" {where} AND v.OrgCode = @orgCode";
                args.Add(new SqlParameter("@orgCode", orgCode));
            }
            var sql = $@"
                    SELECT
	                    *
                    FROM
	                    (
		                    SELECT
			                    v.*,
			                    d1.TypeName CategoryName,
			                    d2.TypeName ,
			                    ROW_NUMBER() OVER(ORDER BY v.CreateTime DESC)rn,
			                    COUNT(1) OVER() TotalCount
		                    FROM
			                    dbo.EVideoLib v
			                    LEFT JOIN dbo.EDictionary d1 ON v.CategoryId = d1.TypeCode AND d1.IsDelete = 0
			                    LEFT JOIN dbo.EDictionary d2 ON v.TypeCode = d2.TypeCode AND d2.IsDelete = 0
		                    WHERE
			                    v.IsDelete = 0  {where}
	                    )a
                    WHERE
	                    a.rn BETWEEN  {request.StartIndex} AND {request.EndIndex}";

            return nContext.Database.SqlQuery<VideoLibModel>(sql, args.ToArray()).ToList();
        }
    }
}


